package project.ccso.top.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import project.ccso.top.dao.CityDao;
import project.ccso.top.entity.City;
import project.ccso.top.entity.Province;
import project.ccso.top.entity.School;
import project.ccso.top.entity.TwoCategory;
import project.ccso.top.util.DBUtil;
import project.ccso.top.util.DateUtil;

/**
 * 城市Dao的实现类
 * 
 * @author aibinxiao
 * @date 2018年8月23日 上午10:37:23
 */
public class CityDaoImpl implements CityDao {
	// 日志
	private static Logger logger = LoggerFactory.getLogger(CityDaoImpl.class);

	private DBUtil dbUtil = new DBUtil();

	/**
	 * 条件查询符合条件的省份记录条数Dao实现类
	 */
	@Override
	public int countCityByParams(String cityId, String cityName, String cityProvinceId, String startDate,String endDate) {
		List<Object> objects = new ArrayList<>();
		int count = 0;
		String sql = "select count(*) from t_city where 1=1";
		if (!StringUtils.isEmpty(cityId)) {
			sql += " and city_id = ?";
			objects.add(cityId);
		}
		if (!StringUtils.isEmpty(cityName)) {
			sql += " and city_name = ?";
			objects.add(cityName);
		}
		if (!StringUtils.isEmpty(cityProvinceId)) {
			sql += " and city_province_id = ?";
			objects.add(cityProvinceId);
		}
		if (!StringUtils.isEmpty(startDate) && !StringUtils.isEmpty(endDate)) {
			startDate += " 00:00:00";
			endDate += " 23:59:59";
			sql += " and city_create_time between ? and ?";
		}
		ResultSet rs = dbUtil.executeQuery(sql, objects);
		try {
			while (rs.next()) {
				count = rs.getInt(1);
			}
			logger.info("找到(" + count + ")个符合查询条件的城市信息");
			return count;
		} catch (SQLException e) {
			logger.info("没有找到符合查询条件求的城市信息");
			e.printStackTrace();
			return count;
		}
	}

	/**
	 * 分页查询符合条件的城市记录Dao实现类
	 */
	@Override
	public List<City> findCityPageByParams(String cityId, String cityName, String provinceId, String startDate, String endDate, int startIndex,
			int records) {
		List<City> result = new ArrayList<>();
		List<Object> objects = new ArrayList<>();
		String sql = "select * from t_city where 1=1 ";

		if (!StringUtils.isEmpty(cityId)) {
			sql += " and city_id = ?";
			objects.add(cityId);
		}
		if (!StringUtils.isEmpty(cityName)) {
			sql += " and city_name = ?";
			objects.add(cityName);
		}
		if (!StringUtils.isEmpty(provinceId)) {
			sql += " and city_province_id = ?";
			objects.add(provinceId);
		}
		if (!StringUtils.isEmpty(startDate) && !StringUtils.isEmpty(endDate)) {
			startDate += " 00:00:00";
			endDate += " 23:59:59";
			sql += " and city_create_time between ? and ?";
		}
		sql += " limit ? , ?";
		objects.add(startIndex);
		objects.add(records);
		ResultSet rs = dbUtil.executeQuery(sql, objects);
		try {
			while (rs.next()) {
				City city = new City(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getInt(4), rs.getString(5), rs.getString(6));
				result.add(city);
			}
		} catch (SQLException e) {
			result = null;
		}
		return result;
	}

	/**
	 * 根据省份Id获取所有的城市Dao实现类*
	 */
	@Override
	public List<City> selectByProvinceId(Integer provinceId) {
		List<City> list = new ArrayList<>();
		String sql = "select * from t_city where city_province_id = ?";
		ResultSet rs = dbUtil.executeQuery(sql, new Object[] { provinceId });
		try {
			while (rs.next()) {
				list.add(new City(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getInt(4), rs.getString(5),
						rs.getString(6)));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	

	/*
	 * // 1.根据省份Id获取所有的城市Dao实现类
	 * 
	 * @Override public List<City> findCitysByProId(Integer provinceId) { String hql
	 * = "from City where provinceId = ? and status = ?"; List<City> result = null;
	 * List<City> list = this.getHibernateTemplate().find(hql, provinceId, 0);
	 * if(list != null && list.size() > 0){ logger.info("所有城市信息集合：" + list); result
	 * = list; } return result; }
	 * 
	 * // 2.根据城市Id获取城市信息Dao实现类
	 * 
	 * @Override public City findCitysById(Integer cityId) { String hql =
	 * "from City where id = ?"; City result = null; List<City> list =
	 * this.getHibernateTemplate().find(hql, cityId); if(list != null && list.size()
	 * > 0){ logger.info("根据城市id获取城市信息：" + list.get(0)); result = list.get(0); }
	 * return result; }
	 * 
	 *//**
		 * 3.根据省份id获取所属的城市信息集合Dao实现类
		 */
	/*
	 * @Override public List<City> findAllCityByProvinceId(Integer id) { String hql
	 * = "from City where provinceId = ?"; List<City> result = null; List<City> list
	 * = this.getHibernateTemplate().find(hql, id); if(list != null && list.size() >
	 * 0){ logger.info("根据省份id:"+id+",查询到所有的城市集合：" + list); result = list; } return
	 * result; }
	 * 
	 *//**
		 * 4.查询符合条件的城市记录数Dao实现类
		 */
	/*
	 * @Override public int countByParams(String id, String name, String startDate,
	 * String endDate, String provinceIdStr) { int count = 0; // 使用离线条件查询.
	 * DetachedCriteria criteria = DetachedCriteria.forClass(City.class);
	 * if(!StringUtils.isEmpty(provinceIdStr)){ Integer provinceId =
	 * Integer.valueOf(provinceIdStr); criteria.add(Restrictions.eq("provinceId",
	 * provinceId)); } if(!StringUtils.isEmpty(id)){ Integer cityId =
	 * Integer.valueOf(id); criteria.add(Restrictions.idEq(cityId)); }
	 * if(!StringUtils.isEmpty(name)){ criteria.add(Restrictions.like("name", name,
	 * MatchMode.ANYWHERE)); } if(!StringUtils.isEmpty(startDate) &&
	 * !StringUtils.isEmpty(endDate)){ startDate += " 00:00:00"; endDate +=
	 * " 23:59:59"; criteria.add(Restrictions.between("createTime", startDate,
	 * endDate)); } // 倒序排序输出: criteria.addOrder(Order.asc("id")); // 执行查询:
	 * List<City> list = this.getHibernateTemplate().findByCriteria(criteria); if
	 * (list != null && list.size() > 0) { count = list.size(); logger.info("找到("+
	 * list.size() +")个符合查询条件的城市"); } else{ logger.info("没有找到符合查询条件求的城市"); } return
	 * count; }
	 * 
	 *//**
		 * 5.分页查询符合条件的城市记录集合Dao实现类
		 */
	/*
	 * @Override public List<City> findPageByParams(String id, String name, String
	 * startDate, String endDate, String provinceIdStr, int startIndex, int records)
	 * { List<City> result = null; // 使用离线条件查询. DetachedCriteria criteria =
	 * DetachedCriteria.forClass(City.class);
	 * if(!StringUtils.isEmpty(provinceIdStr)){ Integer provinceId =
	 * Integer.valueOf(provinceIdStr); criteria.add(Restrictions.eq("provinceId",
	 * provinceId)); } if(!StringUtils.isEmpty(id)){ Integer cityId =
	 * Integer.valueOf(id); criteria.add(Restrictions.idEq(cityId)); }
	 * if(!StringUtils.isEmpty(name)){ criteria.add(Restrictions.like("name", name,
	 * MatchMode.ANYWHERE)); } if(!StringUtils.isEmpty(startDate) &&
	 * !StringUtils.isEmpty(endDate)){ startDate += " 00:00:00"; endDate +=
	 * " 23:59:59"; criteria.add(Restrictions.between("createTime", startDate,
	 * endDate)); } // 倒序排序输出: criteria.addOrder(Order.asc("id")); // 执行查询:
	 * List<City> list = this.getHibernateTemplate().findByCriteria(criteria,
	 * startIndex, records); if (list != null && list.size() > 0) { result = list;
	 * logger.info("找到("+ result.size() +")个符合查询条件的城市"); } else{
	 * logger.info("没有找到符合查询条件求的城市"); } return result; }
	 * 
	 *//**
		 * 6.更新或者保存城市信息Dao实现类
		 */
	/*
	 * @Override public void saveCity(City city) throws Exception {
	 * this.getHibernateTemplate().saveOrUpdate(city); }
	 * 
	 *//**
		 * 7.更新城市信息的启用状态Dao实现类
		 */
	/*
	 * @Override public void updateStauts(Integer id, Integer statusCode) throws
	 * Exception { String hql = "from City where id = ?"; List<City> list =
	 * this.getHibernateTemplate().find(hql, id); if(list!=null && list.size()>0){
	 * City city = list.get(0); city.setStatus(statusCode);
	 * city.setAlterTime(DateUtil.DateToStr(new Date()));
	 * this.getHibernateTemplate().saveOrUpdate(city); } }
	 * 
	 *//**
		 * 8.根据城市id获取所属的高校集合Dao实现类
		 *//*
			 * @Override public List<School> findAllSchoolByCityId(Integer id) { String hql
			 * = "from School where cityId = ?"; List<School> result = null; List<School>
			 * list = this.getHibernateTemplate().find(hql, id); if(list != null &&
			 * list.size() > 0){ logger.info("根据城市id:"+id+",查询到所有的高校集合：" + list); result =
			 * list; } return result; }
			 */
}
